package com.ruoyi.project.newhope.gather;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSON;
import com.ruoyi.project.newhope.data.domain.NewHopeData;
import com.ruoyi.project.newhope.data.domain.NewHopeUnInit;
import com.ruoyi.project.newhope.data.domain.OPCTagRead;
import com.ruoyi.project.newhope.data.mapper.NewHopeCollectDataMapper;
import com.ruoyi.project.newhope.data.mapper.NewHopeUnInitMapper;
import com.ruoyi.project.newhope.device.domain.NewHopeInfraredDevice;
import com.ruoyi.project.newhope.device.mapper.NewHopeInfraredDeviceMapper;
import com.ruoyi.project.newhope.material.domain.NewHopeMaterial;
import com.ruoyi.project.newhope.material.mapper.NewHopeMaterialMapper;
import com.ruoyi.project.newhope.materialInspection.domain.NewHopeMaterialInspection;
import com.ruoyi.project.newhope.materialInspection.mapper.NewHopeMaterialInspectionMapper;
import com.ruoyi.project.newhope.warning.domain.NewHopeWarning;
import com.ruoyi.project.newhope.warning.mapper.NewHopeWarningMapper;
import com.ruoyi.project.newhope.warningLog.domain.NewHopeWarningLog;
import com.ruoyi.project.newhope.warningLog.mapper.NewHopeWarningLogMapper;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;

/**
 * @author xizongren
 * @date 2021/3/4 2:02 下午
 */
@Component
@Slf4j
public class GatherCenter {

    @Autowired
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;

    // 近红外设备Mapper接口
    @Autowired
    private NewHopeInfraredDeviceMapper newHopeInfraredDeviceMapper;

    // 物料主数据设置Mapper接口
    @Autowired
    private NewHopeMaterialMapper newHopeMaterialMapper;

    // 物料检测项目设置(中间[物料主数据设置-化验项目设置])Mapper接口
    @Autowired
    private NewHopeMaterialInspectionMapper newHopeMaterialInspectionMapper;

    // 数据结果收集Mapper接口
    @Autowired
    private NewHopeCollectDataMapper newHopeCollectDataMapper;

    // 数据物料未初始化Mapper接口
    @Autowired
    private NewHopeUnInitMapper newHopeUnInitMapper;

    @Autowired
    private NewHopeWarningMapper newHopeWarningMapper;

    @Autowired
    private NewHopeWarningLogMapper newHopeWarningLogMapper;

    @Value(value = "${sqlserver.jdbcUrl}")
    private String sqlserverJdbcUrl;

    @Value(value = "${sqlserver.user}")
    private String sqlserverUser;

    @Value(value = "${sqlserver.pwd}")
    private String sqlserverPwd;

    @Value(value = "${sqlserver.driverClass}")
    private String sqlserverDriverClass;

    /**
     * 数据处理线程Map维护
     */
    private final Map<String, TaskThread> DEVICE_TASK_MAP = new ConcurrentHashMap<>();

    /**
     * 数据库连接Map集合维护
     */
    private final Map<String, NewHopeInfraredDevice> DEVICE_MAP = new ConcurrentHashMap<>();

    /**
     * new_hope_infrared_device        近红外设备表
     * new_hope_collect_data           近红外采集数据表
     * new_hope_inspection             监测指标【化验项目设置表】
     * new_hope_material               饲料名称【物料主数据设置表】
     * new_hope_material_inspection    关联表【物料检测项目设置表(中间表[物料主数据设置表-化验项目设置表])】
     * <p>
     * new_hope_material     new_hope_inspection       new_hope_infrared_device
     * 物料名称                指标名称                   设备数据
     * 中控SQL                检测系统配置                SQLServer
     * <p>
     * 1、读取指标设备数据库记录【近红外设备表：new_hope_infrared_device】
     * 通过中控SQL查询物料名称，比较有没有在 new_hope_material 出现
     * 2、物料名称有，看有哪些指标【化验项目设置表：new_hope_inspection】
     * 3、物料名称对应的化验项目不为空
     * 固定前缀+指标名称，查询SQLServer（new_hope_infrared_device） => 实时参数
     * 设备状态：数据不更新不读取
     * 4、数据拼接：饲料名称、指标名称、最新值、上下阈值【近红外采集数据表：new_hope_collect_data】
     */
    @PostConstruct
    public void startWork() {
        GatherCenter that = this;
        threadPoolTaskExecutor.execute(() -> {
            while (true) {
                log.info("startWork-刷新可用红外监测设备开始：");
                log.info("startWork-DEVICE_MAP： site={}", DEVICE_MAP.size());
                log.info("startWork-DEVICE_MAP： keys={}", JSON.toJSONString(DEVICE_MAP.keySet()));
                List<NewHopeInfraredDevice> devices = newHopeInfraredDeviceMapper.selectNewHopeInfraredDeviceList(new NewHopeInfraredDevice(1));
                log.info("startWork-当前db有效的的检测点数量={}", devices.size());
                if (devices != null && !devices.isEmpty()) {
                    Map<String, NewHopeInfraredDevice> deviceMapTemp = new HashMap<>();
                    for (NewHopeInfraredDevice device : devices) {
                        deviceMapTemp.put(device.getId() + ":" +device.getHost() + ":" + device.getDbName(), device);
                    }
                    updateDeviceMap(deviceMapTemp);
                    List<String> removeKeys = new ArrayList<>();
                    for (String key : DEVICE_MAP.keySet()) {
                        TaskThread thread = DEVICE_TASK_MAP.get(key);
                        if (thread == null) {
                            log.info("startWork- key={} thread is null 重启采集线程", key);
                            TaskThread taskThread = new TaskThread(key, that);
                            threadPoolTaskExecutor.execute(taskThread);
                            DEVICE_TASK_MAP.put(key, taskThread);
                        } else {
                            checkDevicePropertiesChange(removeKeys, key, thread.getDevice());
                        }
                    }
                    if (removeKeys.size() > 0) {
                        for (String removeKey : removeKeys) {
                            DEVICE_MAP.remove(removeKey);
                            DEVICE_TASK_MAP.remove(removeKey);
                        }
                    }
                    sleepTime(60 * 1000); // 每隔1分钟刷新一次可用红外监测设备
                    log.info("startWork-数据处理线程Map维护, 处理线程数量：{}", DEVICE_TASK_MAP.size());
                } else {
                    DEVICE_MAP.clear();
                }
            }
        });
    }

    private void checkDevicePropertiesChange(List<String> removeKey, String key, NewHopeInfraredDevice device) {
        if (device == null || device.getId() == null) {
            removeKey.add(key);
        }
        NewHopeInfraredDevice newHopeInfraredDevice = newHopeInfraredDeviceMapper.selectNewHopeInfraredDeviceById(device.getId());
        if (newHopeInfraredDevice == null) {
            removeKey.add(key);
        }

        if (!Objects.equals(device.getCode(), newHopeInfraredDevice.getCode())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getLocation(), newHopeInfraredDevice.getLocation())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getLinkSql(), newHopeInfraredDevice.getLinkSql())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getCentralHost(), newHopeInfraredDevice.getCentralHost())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getCentralDbName(), newHopeInfraredDevice.getCentralDbName())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getCentralUser(), newHopeInfraredDevice.getCentralUser())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getCentralPwd(), newHopeInfraredDevice.getCentralPwd())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getHost(), newHopeInfraredDevice.getHost())) {
            removeKey.add(key);
        } else if (!Objects.equals(device.getDbName(), newHopeInfraredDevice.getDbName())) {
            removeKey.add(key);
        }
    }

    /**
     * 任务执行器
     */
    static class TaskThread extends Thread {
        private final String name;
        private final C3P0Utils c3P0Utils;
        private final NewHopeInfraredDevice device;
        private final GatherCenter gatherCenter;
        private final Map<String, TaskThread> deviceTaskMap;
        private final Map<String, NewHopeInfraredDevice> deviceMap;
        private long collectionBatchId; // 物料采集批次（时间戳）
        private String materialCode; // 物料编码
        private String materialMappingValue; // 采集数据物料值
        private NewHopeMaterial material;// 物料对象
        private final C3P0Utils c3P0UtilsLinkSql; // 中控SQL数据库连接池

        public TaskThread(String key, GatherCenter gatherCenter) {
            super(key);
            this.name = key;
            this.gatherCenter = gatherCenter;
            this.deviceTaskMap = gatherCenter.DEVICE_TASK_MAP;
            this.deviceMap = gatherCenter.DEVICE_MAP;
            this.device = deviceMap.get(name);
            // 红外设备 sqlserver 数据库链接
            String redDeviceJdbcUrl = gatherCenter.sqlserverJdbcUrl.replace("$IP", device.getHost() + ":1433").replace("$DB", device.getDbName());
            this.c3P0Utils = new C3P0Utils(redDeviceJdbcUrl, gatherCenter.sqlserverDriverClass, gatherCenter.sqlserverUser, gatherCenter.sqlserverPwd);

            // 中控系统 sqlserver 数据库链接
            String centralJdbcUrl = gatherCenter.sqlserverJdbcUrl.replace("$IP", device.getCentralHost()).replace("$DB", device.getCentralDbName());
            this.c3P0UtilsLinkSql = new C3P0Utils(centralJdbcUrl, gatherCenter.sqlserverDriverClass, device.getCentralUser(), device.getCentralPwd());
        }

        public NewHopeInfraredDevice getDevice() {
            return device;
        }

        /**
         * 工作线程
         * SELECT ident FROM product
         * WHERE oid = (SELECT DISTINCT (product) FROM ingredientcomponent WHERE recipereceiverlist = " &_"(SELECT oid FROM recipe
         * WHERE oid = (SELECT recipeaggregated FROM job LEFT JOIN line ON job.line = line.oid
         * WHERE job.visible = 1" &_"AND job.state = 40 AND line.ident ='ptr3')))
         */
        @SneakyThrows
        @Override
        public void run() {
            log.info("工作线程[ {} ]开始执行：", device.getLocation());
            FINISH:
            while (isContinue("step0")) {
                log.info("step0 {} 进入业务处理", device.getLocation());
                Date isProdEmptyTime = null;
                // step1
                String logStep1 = "step1";
                while (isProdEmpty(logStep1)) {
                    log.info("{} {} while", logStep1, device.getLocation());
                    if (!isContinue(logStep1)) {
                        log.info("{} {} :跳出循环结束当前线程", logStep1, device.getLocation());
                        break FINISH;
                    }
                    collectionBatchId = System.currentTimeMillis();

                    isProdEmptyTime = isProdEmptyTime == null ? new Date() : isProdEmptyTime;
                    if (DateUtil.between(isProdEmptyTime, new Date(), DateUnit.MINUTE, true) >= 3) {
                        log.info("{} {} 超过3分钟没有检测到物料流动结束当前线程", logStep1, device.getLocation());
                        break FINISH;
                    }
                    log.info("{} {} 物料为空，暂不处理！睡眠5s", logStep1, device.getLocation());
                    sleepTime(5L * 1000);
                }

                // step 2
                String logStep2 = "step2";
                while (isContinue(logStep2)) {
                    log.info("{} {} while", logStep2, device.getLocation());
//                    //log.info("1、通过中控SQL查询物料名称，中控SQL：{}", device.getLinkSql());
                    Connection connection = c3P0UtilsLinkSql.getConnection();
                    String centralDBKey = String.format("device=%s中控数据库连接失败", device.getId());
                    if (connection == null) {
                        log.info("{} {} 连接中控数据库连接失败: 中控host={}, 中控数据库={}", logStep2, device.getLocation(), device.getCentralHost(), device.getCentralDbName());
                        //当前中控数据库链接失败
                        NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(centralDBKey);
                        if (newHopeUnInit == null) {
                            // 清理其他的错误
                            gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                            //标记中控数据库连接失败
                            newHopeUnInit = new NewHopeUnInit();
                            newHopeUnInit.setMaterialCode(centralDBKey);
                            newHopeUnInit.setDeviceId(device.getId());
                            newHopeUnInit.setErrMessage(MessageFormat.format("连接中控数据库连接失败: 中控host={0}, 中控数据库={1}", device.getCentralHost(), device.getCentralDbName()));
                            gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                        }
                        sleepTime(1000); // 休眠 1S
                        continue;
                    } else {
                        //删除已经记录的中控数据库连接失败记录
                        log.info("{} {} 删除已经记录的中控数据库连接失败记录", logStep2, device.getLocation());
                        gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(centralDBKey);
                    }
                    List<Object> ident = null;
                    String centralSqlKey = String.format("device=%s中控sql执行失败", device.getId());
                    log.info("{} {} 进行中控sql查询", logStep2, device.getLocation());
                    try {
                        ident = C3P0Utils.query(device.getLinkSql(), connection, new C3P0Utils.ResultHandler() {
                            @Override
                            public <T> T getRow(ResultSet rs, ResultSetMetaData metaData) throws SQLException {
                                return (T) rs.getObject(1);
                            }
                        });
                        log.info("{} {} 中控sql查询完成 结果数据 ident={}", logStep2, device.getLocation(), JSON.toJSONString(ident));
                    } catch (Exception ex) {
                        log.info("执行中控数据库sql查询异常: 中控host={}, 中控数据库={}, 中控sql={}", device.getCentralHost(), device.getCentralDbName(), device.getLinkSql());
                        //标记中控数据库sql执行查询异常
                        NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(centralSqlKey);
                        if (newHopeUnInit == null) {
                            // 清理其他的错误
                            gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                            //标记中控sql查询连失败
                            newHopeUnInit = new NewHopeUnInit();
                            newHopeUnInit.setMaterialCode(centralSqlKey);
                            newHopeUnInit.setDeviceId(device.getId());
                            newHopeUnInit.setErrMessage(MessageFormat.format("查询中控数据库sql失败: 中控host={0}, 中控数据库={1}, 中控sql={2}", device.getCentralHost(), device.getCentralDbName(), device.getLinkSql()));
                            gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                        }
                        sleepTime(1000); // 休眠 1S
                        continue;
                    }
                    //删除已经记录的中控数据库sql失败记录
                    log.info("{} {} 删除已经记录的中控数据库sql失败记录", logStep2, device.getLocation());
                    gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(centralSqlKey);

                    String centralSqlResultKey = String.format("device=%s中控sql执行执行未查询到物料", device.getId());
                    if (!ident.isEmpty() && !"".equals(ident.get(0)) && !"null".equals(ident.get(0))) {
                        log.info("{} {} 删除已经记录的中控数据库sql语句查询物料结果为空记录", logStep2, device.getLocation());
                        gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(centralSqlResultKey);

                        //中控物料
                        materialCode = (String) ident.get(0);
                        NewHopeMaterial newHopeMaterial = new NewHopeMaterial();
                        newHopeMaterial.setMaterialCode(materialCode);
                        List<NewHopeMaterial> list = gatherCenter.newHopeMaterialMapper.selectNewHopeMaterialList(newHopeMaterial);

                        String unInitKey = String.format("device=%s中控物料未在管理系统初始化", device.getId());

                        if (list != null && !list.isEmpty()) {
                            //红外采集物料映射值
                            materialMappingValue = list.get(0).getMaterialMappingValue();
                            material = list.get(0);
                            //更新当前设备正在采集的物料code 到红外设备表中
                            if (!Objects.equals(device.getMaterialCode(), materialCode)) {
                                log.info("{} 中控系统查询的物料编码[{}]与红外设备中的物料编码[{}]不一致，将最新的物料编码更新到红外设备中并删除原有物料已有的报警记录", device.getLocation(), materialCode, device.getMaterialCode());
                                gatherCenter.newHopeWarningMapper.clearWarningByMaterialCode(device.getMaterialCode());
                                gatherCenter.newHopeInfraredDeviceMapper.updateMaterialCodeById(materialCode, device.getId());
                                //将当前最新的物料编码赋予 device 对象中
                                //device.setMaterialCode(materialCode);
                                //deviceMap.get(name).setMaterialCode(materialCode);
                                log.info("{} {} 从deviceTaskMap中");
                                log.info("{} {} 结束当前运行任务：{}. 跳出循环结束当前线程", logStep2, device.getLocation(), name);
                                deviceTaskMap.remove(name);
                                log.info("{} {} 移出deviceTaskMap任务继续执行的任务数量 {}", logStep2, device.getLocation(), deviceMap.size());
                                break FINISH;
                            }
                            //删除已经记录的未初始化记录
                            gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(unInitKey);
                            break;
                        } else {
                            NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(unInitKey);
                            if (newHopeUnInit == null) {
                                // 清理其他的错误
                                gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                                //标记当前物料未初始化
                                newHopeUnInit = new NewHopeUnInit();
                                newHopeUnInit.setMaterialCode(unInitKey);
                                newHopeUnInit.setDeviceId(device.getId());
                                newHopeUnInit.setErrMessage(String.format("中控物料(%s)在软件系统未初始化", materialCode));
                                gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                            }
                        }
                    } else {
                        //标记中控数据库sql执行执行未查询到物料
                        log.info("{} {} 标记中控数据库sql执行执行未查询到物料记录", logStep2, device.getLocation());
                        NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(centralSqlResultKey);
                        if (newHopeUnInit == null) {
                            log.info("{} {} 入库标记中控数据库sql执行执行未查询到物料记录", logStep2, device.getLocation());
                            // 清理其他的错误
                            gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                            //标记中控数据库sql执行执行未查询到物料 入库
                            newHopeUnInit = new NewHopeUnInit();
                            newHopeUnInit.setMaterialCode(centralSqlResultKey);
                            newHopeUnInit.setDeviceId(device.getId());
                            newHopeUnInit.setErrMessage(MessageFormat.format("执行中控数据库sql语句查询物料结果为空: 中控sql={0}", device.getLinkSql()));
                            gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                        }
                    }
                    sleepTime(1000); // 休眠 1S
                }

                //
                List<NewHopeMaterialInspection> list; // 化验项目关联数据
                Map<String, NewHopeMaterialInspection> resultMap = new HashMap<>();
                StringBuilder sql = new StringBuilder("select * from OPCTagRead where ");

                // step3 - 查询检测项目
                String logStep3 = "step3";
                while (isContinue(logStep3)) {
                    log.info("{} {} while", logStep3, device.getLocation());
                    //log.info("2、根据物料code查询化验项目有哪些，化验项目设置表：new_hope_inspection");
                    NewHopeMaterialInspection newHopeMaterialInspection = new NewHopeMaterialInspection();
                    newHopeMaterialInspection.setMaterialCode(materialCode);
                    list = gatherCenter.newHopeMaterialInspectionMapper.selectNewHopeMaterialInspectionList(newHopeMaterialInspection);

                    String unRelationInspectionItemKey = String.format("device=%s中物料未关联检测项目", device.getId());

                    if (CollUtil.isNotEmpty(list)) {
                        //删除已经记录的未关联记录
                        gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(unRelationInspectionItemKey);

                        int idx = 0;
                        for (NewHopeMaterialInspection nhi : list) {
                            String key = "Out/Constituents/" + nhi.getInspectionName().trim();
                            if (idx == 0) {
                                sql.append(" Tag_ID like N'").append(key).append("%'");
                            } else {
                                sql.append(" or Tag_ID like N'").append(key).append("%'");
                            }
                            resultMap.put(key, nhi);
                            idx++;
                        }
                        //log.info("根据物料code查询化验项目结果为：{}", sql);
                        break;
                    } else {
                        //
                        NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(unRelationInspectionItemKey);
                        if (newHopeUnInit == null) {
                            // 清理其他的错误
                            gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                            //标记当前物料未关联检测项目
                            newHopeUnInit = new NewHopeUnInit();
                            newHopeUnInit.setMaterialCode(unRelationInspectionItemKey);
                            newHopeUnInit.setDeviceId(device.getId());
                            newHopeUnInit.setErrMessage(String.format("物料(%s)在软件系统未关联检测项目", material.getMaterialDesc()));
                            gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                        }
                    }

                    log.info("{} {} 根据物料code查询化验项目结果为空！休眠 2s", logStep3, device.getLocation());
                    sleepTime(2000); // 未出现就休眠 1S
                }

                // step 4
                String logStep4 = "step4";
                String updateSql = "update OPCTagWrite set ValNum=?, ValText=? where Tag_ID like N'In/Rawmateria%';";
                while (isContinue(logStep4)) {
                    log.info("{} {} while", logStep4, device.getLocation());
                    Connection connection = c3P0Utils.getConnection();

                    String redDeviceDBKey = String.format("device=%s近红外数据库链接", device.getId());

                    if (connection == null) {
                        log.info("链接近红外数据库连接失败：检测点={}, 近红外编码={}, 近红外数据库host={}, 近红外数据库={}", device.getLocation(), device.getCode(), device.getHost(), device.getDbName());
                        //获取近红外数据库连接失败！
                        NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(redDeviceDBKey);
                        if (newHopeUnInit == null) {
                            // 清理其他的错误
                            gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                            //标记中控数据库连接失败
                            newHopeUnInit = new NewHopeUnInit();
                            newHopeUnInit.setMaterialCode(redDeviceDBKey);
                            newHopeUnInit.setDeviceId(device.getId());
                            newHopeUnInit.setErrMessage(MessageFormat.format("链接近红外数据库连接失败：近红外数据库host={0}, 近红外数据库={1}", device.getHost(), device.getDbName()));
                            gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                        }
                        sleepTime(1000); // 休眠 1S
                        continue;
                    } else {
                        //删除已经记录链接近红外数据库连接失败记录
                        log.info("删除已经记录链接近红外数据库连接失败记录");
                        gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(redDeviceDBKey);
                    }

                    // 写数据到 OPCTagWrite
                    int updateNum = C3P0Utils.update(updateSql, connection, ps -> {
                        try {
                            ps.setInt(1, Integer.parseInt(materialMappingValue));
                            ps.setString(2, materialMappingValue);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    });
                    if (updateNum != -1) {
                        log.info("{} {} 更新 OPCTagWrite 成功！", logStep4, device.getLocation());
                        break;
                    }
                    log.info("{} {} 更新 OPCTagWrite 失败，等待再次重试！休眠 2s", logStep4, device.getLocation());
                    sleepTime(2000); // 休眠 1S
                }

                long lastMills = 1L; // 最后一次采集时间戳

                // step 5
                String logStep5 = "step5";
                NO:
                while (isContinue(logStep5)) {
                    log.info("{} {} while", logStep5, device.getLocation());
                    // step 5.1 物料是否流动
                    Date noFlowTime = null;
                    String logStep5_1 = "step5.1";
                    while (isProdNoFlow(logStep5_1)) {
                        log.info("{} {} while", logStep5_1, device.getLocation());
                        if (!isContinue(logStep5_1)) {
                            log.info("{} {} 结束当前线程跳出最外层while循环", logStep5_1, device.getLocation());
                            break FINISH;
                        }
                        try {
                            if (isMaterialChange(logStep5_1, materialCode)) {
                                log.info("{} {} 物料已切换(1.真的切换了，2.异常情况)，跳出循环结束当前线程", logStep5_1, device.getLocation());
                                break FINISH;
                            }
                        } catch (Exception ex) {
                            ex.printStackTrace();
                            log.error("{} {} 执行物料是否切换判断逻辑异常, 跳出循环结束当前线程", logStep5_1, device.getLocation());
                            log.info("{} {} 执行物料是否切换判断逻辑异常, 跳出循环结束当前线程", logStep5_1, device.getLocation());
                            break FINISH;
                        }

                        noFlowTime = noFlowTime == null ? new Date() : noFlowTime;
                        if (DateUtil.between(noFlowTime, new Date(), DateUnit.MINUTE, true) >= 3) {
                            log.info("{} {} 超过3分钟没有检测到物料流动结束当前线程", logStep5_1, device.getLocation());
                            break FINISH;
                        }
                        log.info("{} {} -暂停数据采集状态！", logStep5_1, device.getLocation());
                        sleepTime(3L * 1000);
                    }

                    if (isProdEmpty(logStep5_1)) {
                        log.info("{} {} - 物料为空，跳出循环！", logStep5_1, device.getLocation());
                        break;
                    }

                    Connection connection = c3P0Utils.getConnection();
                    if (connection == null) {
                        log.info("{} {} -获取数据连接失败！", logStep5_1, device.getLocation());
                        sleepTime(1000); // 休眠 1S
                        continue;
                    }
                    List<OPCTagRead> record = C3P0Utils.queryTag(connection, sql.toString());
                    if (record.isEmpty()) {
                        log.info("{} {} -未读取到数据！", logStep5_1, device.getLocation());
                        sleepTime(1000); // 休眠 1S
                        continue;
                    }
                    if (lastMills == record.get(0).getOpcTime().getTime()) {
                        log.info("{} {} -数据未更新！", logStep5_1, device.getLocation());
                        sleepTime(1000); // 休眠 1S
                        continue;
                    }
                    lastMills = record.get(0).getOpcTime().getTime();
                    //log.info("4、数据拼接：饲料名称、指标名称、最新值、上下阈值，存储到近红外采集数据表（new_hope_collect_data）");
                    List<NewHopeData> newHopeDataList = new ArrayList<>();
                    for (OPCTagRead read : record) {
                        if (read.getValNum() == null) {
                            log.info("{} {} -检测值为 null", logStep5_1, device.getLocation());
                            continue;
                        }
                        NewHopeData data = new NewHopeData();
                        NewHopeMaterialInspection newHopeMaterialInspection = resultMap.get(read.getTagId().trim());
                        if (newHopeMaterialInspection == null) {
                            log.info("{} {} -物料检测项目为空", logStep5_1, device.getLocation());
                            continue;
                        }
                        //有可能中途去修改了最大值和最小值，重新查询对应的指标
                        NewHopeMaterialInspection hopeMaterialInspection = gatherCenter.newHopeMaterialInspectionMapper.selectNewHopeMaterialInspectionById(newHopeMaterialInspection.getId());

                        BeanUtils.copyProperties(newHopeMaterialInspection, data);
                        data.setMaterialName(newHopeMaterialInspection.getMaterialDesc());
                        data.setDeviceId(device.getId());
                        data.setDeviceLocation(device.getLocation());
                        data.setInspectionValue(read.getValNum() * 1.0);
                        data.setCheckTime(read.getOpcTime());
                        data.setBatchNum(String.valueOf(collectionBatchId));
                        data.setCreateTime(new Date());
                        data.setUpdateTime(new Date());
                        data.setMin(hopeMaterialInspection.getMin());
                        data.setMax(hopeMaterialInspection.getMax());
                        newHopeDataList.add(data);
                        // 判断指标是否报警正常
                        isInspectionOutRange(data);
                    }
                    if (newHopeDataList.size() == 0) {
                        continue;
                    }
                    try {
                        log.info("{} 插入数据 检测点={} 物料={}", logStep5_1, newHopeDataList.get(0).getDeviceLocation(), newHopeDataList.get(0).getMaterialName());
                        gatherCenter.newHopeCollectDataMapper.batchInsert(newHopeDataList);
                        //log.info("批量插入采集数据 json={}", JSON.toJSONString(newHopeDataList));
                    } catch (Exception e) {
                        log.error("{} {} 批量插入数据异常：", logStep5_1, device.getLocation(), e);
                    }
                    sleepTime(1000); // 休眠 1S
                }
            }
            //清除设备记录&任务记录
            gatherCenter.DEVICE_TASK_MAP.remove(name);
            gatherCenter.DEVICE_MAP.remove(name);
            log.info("{} {} 线程结束>>>>>>>>>>>>>>>>", "step0", device.getLocation());
        }

        /**
         * 采集指标是否不在指定范围
         * @param data
         */
        private void isInspectionOutRange(NewHopeData data) {
            Double inspectionValue = data.getInspectionValue();
            if (inspectionValue < data.getMin() || inspectionValue > data.getMax()) {
                //log.info("存在报警指标, min={},max={},报警值={}", data.getMin(), data.getMax(), inspectionValue);
                // 报警当前指标
                NewHopeWarningLog newHopeWarningLog = new NewHopeWarningLog();
                newHopeWarningLog.setInfraredDeviceId(data.getDeviceId());
                newHopeWarningLog.setMaterialId(data.getMaterialId());
                newHopeWarningLog.setInspectionId(data.getInspectionId());
                newHopeWarningLog.setAlarmValue(inspectionValue);
                gatherCenter.newHopeWarningLogMapper.insertNewHopeWarningLog(newHopeWarningLog);

                // 如果当前指标已经存在不在记录插入报警数据
                NewHopeWarning newHopeWarning = new NewHopeWarning();
                newHopeWarning.setInfraredDeviceId(data.getDeviceId());
                newHopeWarning.setMaterialId(data.getMaterialId());
                newHopeWarning.setInspectionId(data.getInspectionId());
                newHopeWarning.setAlarmValue(inspectionValue);
                newHopeWarning.setStepSwitch(data.getStepSwitch());

                NewHopeWarning exist = gatherCenter.newHopeWarningMapper.selectNewHopeWarningIsExist(newHopeWarning);
                if (exist!=null) {
                    exist.setAlarmValue(newHopeWarning.getAlarmValue());
                    gatherCenter.newHopeWarningMapper.updateNewHopeWarning(exist);
                    //log.info("更新报警数据：{}", JSON.toJSONString(newHopeWarning));
                } else {
                    gatherCenter.newHopeWarningMapper.insertNewHopeWarning(newHopeWarning);
                    //log.info("新增报警数据：{}", JSON.toJSONString(newHopeWarning));
                }
            } else {
                // 删除已经的报警指标
                gatherCenter.newHopeWarningMapper.deleteNewHopeWarning(data.getDeviceId(), data.getMaterialId(), data.getInspectionId());
            }
        }

        /**
         * 物料不流动不更新数据
         */
        private boolean isProdNoFlow(String step) {
            try {
                log.info("{} {} isProdNoFlow 物料流动判断-start", step, device.getLocation());
                String noProductFlowSql = "select * from OPCTagRead where Tag_ID = 'Out/Warnings/NoProductFlow';";
                Connection connection = c3P0Utils.getConnection();
                /*
                if (connection == null) {
                    log.info("{} {} isProdNoFlow 获取链接为null", step, device.getLocation());
                    return true;
                }
                */
                String materialIsProdNoFlowKey = String.format("device=%s物料不流动", device.getId());
                if (connection == null) {
                    log.info("{} {} 获取connection为null: 近红外host={}, 近红外数据库={}", step, device.getLocation(), device.getHost(), device.getDbName());
                    NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(materialIsProdNoFlowKey);
                    if (newHopeUnInit == null) {
                        // 清理其他的错误
                        gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                        //标记红外数据库链接失败
                        newHopeUnInit = new NewHopeUnInit();
                        newHopeUnInit.setMaterialCode(materialIsProdNoFlowKey);
                        newHopeUnInit.setDeviceId(device.getId());
                        newHopeUnInit.setErrMessage(String.format("连接近红外数据库连接失败: 近红外host=%s, 近红外数据库=%s", device.getHost(), device.getDbName()));
                        gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                    }
                    return true;
                } else {
                    //删除已标记的红外数据库链接记录
                    log.info("{} {} 删除已标记的获取红外数据库链接connection为null记录", step, device.getLocation());
                    gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(materialIsProdNoFlowKey);
                }


                List<OPCTagRead> npfList = C3P0Utils.queryTag(connection, noProductFlowSql);

                String redDeviceNoMaterialKey = String.format("device=%s近红外物料", device.getId());

                // 物料流动跳出循环采集数据
                if (!npfList.isEmpty() && npfList.get(0).getValNum() == 0) {
                    //删除已标记的斜坡板没有物料记录
                    log.info("{} {} isProdNoFlow: 物料流动中, 删除记录", step, device.getLocation());
                    gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(redDeviceNoMaterialKey);
                    return false;
                } else {
                    log.info("{} {} isProdNoFlow: 物料没有流动", step, device.getLocation());
                    NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(redDeviceNoMaterialKey);
                    if (newHopeUnInit == null) {
                        log.info("{} {} isProdNoFlow: 红外没有物料，将记录写进异常表", step, device.getLocation());
                        // 清理其他的错误
                        gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                        //标记当前斜坡板没有物料
                        newHopeUnInit = new NewHopeUnInit();
                        newHopeUnInit.setMaterialCode(redDeviceNoMaterialKey);
                        newHopeUnInit.setDeviceId(device.getId());
                        newHopeUnInit.setErrMessage("当前没有检测到有物料流动！");
                        gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
                log.error("{} {} isProdNoFlow 异常", step, device.getLocation(), e);
            }
            log.info("{} {} isProdNoFlow 物料流动判断-end", step, device.getLocation());
            return true;
        }

        /**
         * 近红外设备是否没有检测到物料生成
         *
         * @param step
         * @return true-有处于生产中的物料 false-没有有处于生产中的物料
         */
        private boolean isProdEmpty(String step) {
            try {
                log.info("{} {} isProdEmpty 物料为空判断-start", step, device.getLocation());
                String productAvailableSql = "select * from OPCTagRead where Tag_ID = 'Out/Status/ProductAvailable';";
                Connection connection = c3P0Utils.getConnection();
                String redDeviceSqlKey = String.format("device=%s近红外数据库查询", device.getId());
                if (connection == null) {
                    log.error("{} {} 查询近红外采集数据失败: 近红外host={}, 近红外数据库={}, 查询sql={}", step, device.getLocation(), device.getHost(), device.getDbName(), productAvailableSql);
                    NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(redDeviceSqlKey);
                    if (newHopeUnInit == null) {
                        // 清理其他的错误
                        gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                        //标记红外数据库链接失败
                        newHopeUnInit = new NewHopeUnInit();
                        newHopeUnInit.setMaterialCode(redDeviceSqlKey);
                        newHopeUnInit.setDeviceId(device.getId());
                        newHopeUnInit.setErrMessage(String.format("连接近红外数据库连接失败: 近红外host=%s, 近红外数据库=%s, 查询sql=%s", device.getHost(), device.getDbName(), productAvailableSql));
                        gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                    }
                    return true;
                } else {
                    //删除已标记的红外数据库链接记录
                    log.info("{} {} 删除已标记的红外数据库链接记录", step, device.getLocation());
                    gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(redDeviceSqlKey);
                }
                log.info("{} {} 查询近红外物料sql = {}", step, device.getLocation(), productAvailableSql);
                List<OPCTagRead> pvList = C3P0Utils.queryTag(connection, productAvailableSql);
                log.info("{} {} 物料是否为空状态：{}", step, device.getLocation(), CollUtil.isNotEmpty(pvList) ? JSON.toJSONString(pvList) : "空");
                // 物料不为空，开始新一轮的采集。采集流程重新开始
                // Out/Status/ProductAvailable'：0 没有产品；-1 有产品

                String redDeviceNoMaterialKey = String.format("device=%s近红外物料", device.getId());

                if (!pvList.isEmpty() && pvList.get(0).getValNum() != null && pvList.get(0).getValNum() == -1) {
                    //删除已标记的斜坡板没有物料记录
                    log.info("{} {} isProdEmpty 红外有物料了", step, device.getLocation());
                    gatherCenter.newHopeUnInitMapper.deleteByMaterialCode(redDeviceNoMaterialKey);
                    return false;
                } else {
                    log.info("{} {} isProdEmpty： 红外没有物料", step, device.getLocation());
                    NewHopeUnInit newHopeUnInit = gatherCenter.newHopeUnInitMapper.selectByMaterialCode(redDeviceNoMaterialKey);
                    if (newHopeUnInit == null) {
                        log.info("{} {} isProdEmpty： 红外没有物料，将记录写进异常表", step, device.getLocation());
                        // 清理其他的错误
                        gatherCenter.newHopeUnInitMapper.clear(String.format("device=%s", device.getId()));
                        //标记当前斜坡板没有物料
                        newHopeUnInit = new NewHopeUnInit();
                        newHopeUnInit.setMaterialCode(redDeviceNoMaterialKey);
                        newHopeUnInit.setDeviceId(device.getId());
                        newHopeUnInit.setErrMessage("当前没有检测到有物料流动！");
                        gatherCenter.newHopeUnInitMapper.insert(newHopeUnInit);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
                log.error("{} {} isProdEmpty 异常", step, device.getLocation(), e);
            }
            log.info("{} {} isProdEmpty 物料为空判断-end", step, device.getLocation());
            return true;
        }

        /**
         * 物料是否切换
         */
        private boolean isMaterialChange(String step, String materialCode) throws SQLException {
            log.info("{} {} 物料是否切换-start", step, device.getLocation());
            Connection connection = c3P0UtilsLinkSql.getConnection();
            if (connection == null) {
                log.info("{} {} 判断中控物料是否切换，获取数据连接connetion为null，标识物料已切换", step, device.getLocation());
                return true;
            }
            List<Object> ident = null;
            try {
                ident = C3P0Utils.query(device.getLinkSql(), connection, new C3P0Utils.ResultHandler() {
                    @Override
                    public <T> T getRow(ResultSet rs, ResultSetMetaData metaData) throws SQLException {
                        return (T) rs.getObject(1);
                    }
                });
            } catch (Exception e) {
                e.printStackTrace();
                log.error("{} {} 物料是否切换：执行中控数据库sql异常", step, device.getLocation());
                log.info("{} {} 物料是否切换：执行中控数据库sql异常", step, device.getLocation());
                throw e;
            }
            String newMaterialCode = null;
            if (ident != null && !ident.isEmpty() && !"".equals(ident.get(0)) && !"null".equals(ident.get(0))) {
                newMaterialCode = (String) ident.get(0);
                if (Objects.equals(newMaterialCode, materialCode)) {
                    log.info("{} {} 两次中控物料一致", step, device.getLocation());
                    return false;
                }
            }
            //根据最新物料查询是否存在于物料表中
            NewHopeMaterial material = gatherCenter.newHopeMaterialMapper.selectByMaterialCode(newMaterialCode);
            if (material == null) {
                log.info("{} {} 当前中控物料未在系统中进行配置", step, device.getLocation());
                return true;
            }
            log.info("{} {} 物料是否切换-end", step, device.getLocation());
            return true;
        }

        /**
         * 当前设备是否已经被移除
         * @param step
         * @return true-没有被移除 false-已被移除
         */
        private synchronized boolean isContinue(String step) {
            log.info("{} {} isContinue 检测是否继续-start ", step, device.getLocation());
            NewHopeInfraredDevice newHopeInfraredDevice = deviceMap.get(name);
            if (newHopeInfraredDevice == null) {
                deviceTaskMap.remove(name);
                log.info("{} {} 当前监测设备数据库[ {} ]已经被移除！当前运行任务线程数量：{}. 跳出循环结束当前线程", step, device.getLocation(), name, deviceTaskMap.size());
                return false;
            }
            NewHopeInfraredDevice newDevice = gatherCenter.newHopeInfraredDeviceMapper.selectNewHopeInfraredDeviceById(device.getId());
            if (!isDevicePropertiesChange(newDevice, device)) {
                log.info("{} {} 近红外设备属性配置已经更改，跳出循环结束当前线程", step, device.getLocation());
                return false;
            }
            log.info("{} {} isContinue 检测是否继续-end ", step, device.getLocation());
            return true;
        }
    }

    /**
     * 经红外设备属性配置是否 change
     *
     * @param newDevice
     * @param oldDevice
     * @return
     */
    private static boolean isDevicePropertiesChange(NewHopeInfraredDevice newDevice, NewHopeInfraredDevice oldDevice) {
        if (newDevice == null) {
//            //log.info("isContinue-查询数据库红外设备device=null, 从 map 中移除");
            return false;
        }
        if (!Objects.equals(oldDevice.getCode(), newDevice.getCode())) {
//            //log.info("isContinue-查询数据库红外设备-近红外编码 change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getLocation(), newDevice.getLocation())) {
//            //log.info("isContinue-查询数据库红外设备-检测点 change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getLinkSql(), newDevice.getLinkSql())) {
//            //log.info("isContinue-查询数据库红外设备-中控 sql change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getCentralHost(), newDevice.getCentralHost())) {
//            //log.info("isContinue-查询数据库红外设备-中控 host change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getCentralDbName(), newDevice.getCentralDbName())) {
//            //log.info("isContinue-查询数据库红外设备-中控 dbName change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getCentralUser(), newDevice.getCentralUser())) {
//            //log.info("isContinue-查询数据库红外设备-中控 pwd change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getCentralPwd(), newDevice.getCentralPwd())) {
//            //log.info("isContinue-查询数据库红外设备-中控 pwd change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getHost(), newDevice.getHost())) {
//            //log.info("isContinue-查询数据库红外设备-红外 host change, 从 map 中移除");
            return false;
        } else if (!Objects.equals(oldDevice.getDbName(), newDevice.getDbName())) {
//            //log.info("isContinue-查询数据库红外设备-红外 dbName change, 从 map 中移除");
            return false;
        }
        return true;
    }

    /**
     * 更新设备Map
     */
    private synchronized void updateDeviceMap(Map<String, NewHopeInfraredDevice> deviceMapTemp) {
        log.info("startWork-updateDeviceMap       deviceMapTemp={}", JSON.toJSONString(deviceMapTemp.keySet()));
        log.info("startWork-updateDeviceMap          DEVICE_MAP={}", JSON.toJSONString(DEVICE_MAP.keySet()));
        for (String key : DEVICE_MAP.keySet()) {
            if (deviceMapTemp.get(key) == null) {
                DEVICE_MAP.remove(key);
            }
        }
        for (String key : deviceMapTemp.keySet()) {
            if (DEVICE_MAP.get(key) == null) {
                DEVICE_MAP.put(key, deviceMapTemp.get(key));
            }
        }
        log.info("startWork-updateDeviceMap 最终-DEVICE_MAP keys={}", JSON.toJSONString(DEVICE_MAP.keySet()));
    }

    /**
     * 睡眠等待
     */
    public static void sleepTime(long time) {
        try {
//            //log.info("开始睡眠，休眠时间 - {}分:{}秒.", (time / 1000 / 60), (time / 1000 - (time / 1000 / 60) * 60));
            //Thread.sleep(time);
            TimeUnit.SECONDS.sleep(time / 1000);
        } catch (Exception e) {
            //log.info("休眠 exception 捕获");
//            e.printStackTrace();
        }
//        //log.info("休眠结束！");
    }
}
